package com.graduationpractice.dbot.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpSession;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * <p>
 *  前端控制器
 * </p>
 *
 * @author ${author}
 * @since 2022-03-24
 */
@Controller
public class OperationlogController {

    @RequestMapping("Operationlog")
    public String tableQuery(HttpSession session) {

        String sql="select * from operationlog";

        //清空session
        session.removeAttribute("pojolist");
        session.removeAttribute("failStr");


        List<List<String>> pojolist = new ArrayList<>();//存储表内数据

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String failStr = "查询失败";

        try {
            //1.注册驱动
            //反射注册驱动
            String driver = "com.mysql.cj.jdbc.Driver";
            Class.forName(driver);
            //2.数据库连接
            String url = "jdbc:mysql://"+session.getAttribute("Address").toString()+"/dbotbase"+"?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false";
            String username = session.getAttribute("databaseuser").toString();
            String password = session.getAttribute("databasepsw").toString();
            conn = DriverManager.getConnection(url, username, password);

            //3.获取数据库操作对象
            stmt = conn.createStatement();

            //4.执行sql语句
            //ResultSet ecxecuteQuery(select) 返回值ResultSet
            //int executrUpdate(insert/delete/update)  返回值是“影响数据库中的记录条数“

            //获取查询结果集ResultSet
            rs = stmt.executeQuery(sql);
            ResultSetMetaData metaData = rs.getMetaData();

            //获取列数
            int colCount = metaData.getColumnCount();

            //5.处理查询结果集（select查询时使用）
            //getString()方法的特点时不管数据库中的数据类型是什么，都以String形式取出
            while (rs.next()) {
                List<String> pojoString = new ArrayList<>();
                for (int i = 1; i <= colCount; i++) {
                    pojoString.add(rs.getString(i));
                }
                pojolist.add(pojoString);
                System.out.println(pojoString);
            }
            System.out.println(pojolist);

            //发送数据
            session.setAttribute("pojolist", pojolist);

        } catch (Exception e) {
            //程序异常输出’查询失败‘字段
            session.setAttribute("failStr", failStr);
            e.printStackTrace();
        } finally {
            //6.释放资源
            //为了保证资源一定释放，在finally语句块中关闭资源
            //并且遵循从小到大依次关闭，分别对其try..catch...
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return "operationlog";
    }
}

